package com.zhiyou100.crm.dao.impl;

import com.zhiyou100.crm.dao.NoticeDao;
import com.zhiyou100.crm.util.DBUtil1;
import com.zhiyou100.crm.util.Notice;
import com.zhiyou100.crm.util.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class NoticeDaoImpl implements NoticeDao {
    DBUtil1 dbUtil1=new DBUtil1();
    ResultSet rs=null;
    Notice notice;

    @Override
    public List<Notice> queryAllNotice() {
        List<Notice> notices=new ArrayList<>();
//        String sql="select * from notice";
        String sql="select a.*,b.username from notice as a,user as b where a.creater=b.creater";
        rs=dbUtil1.select(sql,null);
        try {
            while (rs.next()) {
                int notice_id = rs.getInt("notice_id");
                int receive_id = rs.getInt("receive_id");
                String subject = rs.getString("subject");
                String text = rs.getString("text");
                Timestamp pub_time = rs.getTimestamp("pub_time");
                Timestamp expire_time = rs.getTimestamp("expire_time");
                int status = rs.getInt("status");
                String remark = rs.getString("remark");
                Timestamp create_time = rs.getTimestamp("create_time");
                int creater = rs.getInt("creater");
                Timestamp update_time=rs.getTimestamp("update_time");
                int updater = rs.getInt("updater");
                String username = rs.getString("username");
                notice=new Notice(notice_id,receive_id,subject,text,pub_time,expire_time,status,remark,create_time,creater,update_time,updater,username);
                notices.add(notice);
            }
            return notices;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Boolean removeById(int noticeId) {

        String sql="delete from notice where notice_id=?";
        Object[] obj={noticeId};
        int count=dbUtil1.update(sql,obj);
        if(count>0){
            return true;
        }else{
            return false;
        }
    }
    @Override
    public Notice queryNoticeById(int notice_id) {
        String sql="select * from notice where notice_id=?";
        Object[] obj={notice_id};
        rs=dbUtil1.select(sql,obj);
        try {
            if (rs.next()) {
                int notice_id1 = rs.getInt("notice_id");
                int receive_id = rs.getInt("receive_id");
                String subject=rs.getString("subject");
                String text = rs.getString("text");
                Timestamp pub_time = rs.getTimestamp("pub_time");
                Timestamp expire_time = rs.getTimestamp("expire_time");
                int status = rs.getInt("status");
                String remark=rs.getString("remark");
                Timestamp create_time = rs.getTimestamp("create_time");
                int creater = rs.getInt("creater");
                Timestamp update_time = rs.getTimestamp("update_time");
                int updater = rs.getInt("updater");
                notice=new Notice(notice_id1,receive_id,subject,text,pub_time,expire_time,status,remark,create_time,creater,update_time,updater);
            }
            return notice;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Notice> queryNoticeByFile(String field) {
        List<Notice> notices=new ArrayList<>();
        String sql = "select a.*,b.username from notice as a,user as b where a.creater=b.creater and subject like ?";
        Object[] obj = {field};
        rs = dbUtil1.select(sql, obj);
        try {
            while (rs.next()) {
                int notice_id1 = rs.getInt("notice_id");
                int receive_id = rs.getInt("receive_id");
                String subject = rs.getString("subject");
                String text = rs.getString("text");
                Timestamp pub_time = rs.getTimestamp("pub_time");
                Timestamp expire_time = rs.getTimestamp("expire_time");
                int status = rs.getInt("status");
                String remark = rs.getString("remark");
                Timestamp create_time = rs.getTimestamp("create_time");
                int creater = rs.getInt("creater");
                Timestamp update_time = rs.getTimestamp("update_time");
                int updater = rs.getInt("updater");
                String username = rs.getString("username");
                notice = new Notice(username,notice_id1, receive_id, subject, text, pub_time, expire_time, status, remark, create_time, creater, update_time, updater);
                notices.add(notice);
            }
            return notices;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    @Override
    public int addNotice(Notice notice, User user) {
        String sql="insert into notice(pub_time,expire_time,subject,remark,text,creater) values(?,?,?,?,?,?)";
        Object[] obj={notice.getPub_time(),notice.getExpire_time(),notice.getSubject(),notice.getRemark(),notice.getText(),user.getCreater()};
        return dbUtil1.update(sql, obj);

    }

    @Override
    public int upadteNotice(Notice notice,int id) {
        String sql="update notice set pub_time=?,expire_time=?,subject=? ,text=?,receive_id=? where notice_id=?";
        Object[] obj={notice.getPub_time(),notice.getPub_time(),notice.getSubject(),notice.getText(),notice.getReceive_id(),id};
        return dbUtil1.update(sql,obj);
    }
}
